<? /* 	These code was generated using phpCIGen v 0.1.a (21/04/2009)
	#zaqi 		zaqi.smart@gmail.com,http://zenzaqi.blogspot.com, 
    #songbee	mukhlisona@gmail.com
	#CV. Trust Solution, jl. Saronojiwo 19 Surabaya, http://www.ts.co.id
	
	+ Module  		: m_job Model
	+ Description	: For record model process back-end
	+ Filename 		: c_m_job.php
 	+ Author  		: 
 	+ Created on 24/Jul/2012 11:02:41
	
*/

class M_m_job extends CI_Model{
		
		//constructor
		function M_m_job() {
			parent::__construct();
		}
		
		//function for detail
		//get record list
		function detail_m_jobdet_list($master_id,$query,$start,$end) {
			
			$query = "SELECT * FROM m_jobdet WHERE m_job_id='".$master_id."'";
			$result = $this->db->query($query);
			$nbrows = $result->num_rows();
			$limit = $query." LIMIT ".$start.",".$end;			
			$result = $this->db->query($limit);  
			
			if($nbrows>0){
				foreach($result->result() as $row){
					$arr[] = $row;
				}
				$jsonresult = json_encode($arr);
				return '({"total":"'.$nbrows.'","results":'.$jsonresult.'})';
			} else {
				return '({"total":"0", "results":""})';
			}
		}
		//end of function
		
		//insert detail record
		function detail_m_jobdet_insert($master_id, $array_jobdet_id, $array_jobdet_name){
			//delete data detail by $master_id
			$sqld = "DELETE FROM m_jobdet WHERE m_job_id=".$master_id;
			$this->db->query($sqld);
			
			if($master_id==0){
				return 0;
			}else{
				$query="";
				for($i = 0; $i < sizeof($array_jobdet_name); $i++){
					$jobdet_id = $array_jobdet_id[$i];
					$jobdet_name = $array_jobdet_name[$i];
			
					$data = array(
						"m_job_id"=>$master_id,
						"jobdet_name"=>$jobdet_name, 
						"jobdet_active"=>'Y'
					);
					$this->db->insert('m_jobdet', $data);
				}
				return 1;
			}

		}
		//end of function
		
		//function for get list record
		function m_job_list($filter,$start,$end){
		
			$sql = "call prepare_tablesplit";
			$this->db->query($sql);
			$sql = "SELECT job_id, job_name, m_div_id FROM m_job";
			$query = $this->db->query($sql);
			foreach($query->result()  as $result){
				$sql = "call split_string('".$result->m_div_id."',',',".$result->job_id.");";
				$this->db->query($sql);
			}
			
			$query = "SELECT * FROM (SELECT job_id, job_kode,job_name, job_active, div_id, m_div_id, 
					group_concat(div_name) as div_name, dvo_id, dvo_name
					FROM m_job LEFT JOIN splitResults ON (job_id = split_id) 
					LEFT JOIN m_div ON(m_div.div_id = splitResults.split_value)
					LEFT JOIN m_dvo ON(m_dvo.dvo_id = m_job.m_dvo_id)
					GROUP BY job_id) t ";
			
			// $query = "SELECT job_id, job_kode,job_name, job_active, div_id, div_name, dvo_id, dvo_name
					// FROM m_job LEFT JOIN m_div ON(m_div.div_id = m_job.m_div_id)
					// LEFT JOIN m_dvo ON(m_dvo.dvo_id = m_job.m_dvo_id)";

			// For simple search
			if ($filter<>""){
				$query .=preg_match("/\bWHERE\b/i",$query)? " AND ":" WHERE ";
				$query .= " (job_kode LIKE '%".addslashes($filter)."%' OR job_name LIKE '%".addslashes($filter)."%' )";
			}
			
			$result = $this->db->query($query);
			$nbrows = $result->num_rows();
			$limit = $query." LIMIT ".$start.",".$end;		
			$result = $this->db->query($limit);  
			
			if($nbrows>0){
				foreach($result->result() as $row){
					$arr[] = $row;
				}
				$jsonresult = json_encode($arr);
				return '({"total":"'.$nbrows.'","results":'.$jsonresult.'})';
			} else {
				return '({"total":"0", "results":""})';
			}
		}
		
		//function for create new record
		function m_job_create($job_kode,$job_name ,$job_active ,$job_div, $array_jobdet_id, $array_jobdet_name){
			//checking $job_div <== Jika "numeric" maka insert langsung ke db.m_job; Jika "string" maka insert ke db.m_div
			// if(!(is_numeric($job_div))){
				// $job_div = $this->m_public_function->m_div_create($job_div, 'Y');
			// }
			
			$data = array(
				"job_kode"=>$job_kode, 
				"job_name"=>$job_name, 
				"job_active"=>$job_active, 
				"m_div_id"=>$job_div
			);
			$this->db->insert('m_job', $data); 
			if($this->db->affected_rows()){
				$job_id = $this->db->insert_id();
				$this->detail_m_jobdet_insert($job_id, $array_jobdet_id, $array_jobdet_name);
				return $job_id;
			}else{
				return '0';
			}
		}
		
		//function for update record
		function m_job_update($job_id,$job_kode,$job_name,$job_active,$job_updated_date,$job_updated_by,$job_updated_count,$m_div_id,$array_jobdet_id, $array_jobdet_name){
			$kuery_getdivid = "SELECT div_id FROM m_div WHERE div_name = '". $m_div_id ."'";
			$query_getdivid = $this->db->query($kuery_getdivid);
			if($query_getdivid->num_rows() > 0){
				$row = $query_getdivid->row();
				$m_div_id = $row->div_id;
			}
			
			$affected_masterjob = 0;
			
			$data = array(
				"job_kode"=>$job_kode, 
				"job_name"=>$job_name, 
				"job_active"=>$job_active, 
				"job_updated_date"=>$job_updated_date, 
				"job_updated_by"=>$job_updated_by, 
				"job_updated_count"=>$job_updated_count,
				"m_div_id"=>$m_div_id 
			);
			
			$this->db->where('job_id', $job_id);
			$this->db->update('m_job', $data);
			if($this->db->affected_rows()){
				$affected_masterjob = 1;
			}
			
			$this->detail_m_jobdet_insert($job_id, $array_jobdet_id, $array_jobdet_name);
			if($this->db->affected_rows()){
				return "1";
			}else{
				if($affected_masterjob == 1){
					return "1";
				}else{
					return "0";
				}
			}
			$sql="UPDATE m_job SET job_updated_count=(job_updated_count + 1) 
					WHERE job_id=".$job_id." AND m_div_id=".$m_div_id;
			$this->db->query($sql);
			return "1";
		}
		
		//fcuntion for delete record
		function m_job_delete($pkid){
			// You could do some checkups here and return '0' or other error consts.
			// Make a single query to delete all of the m_jobs at the same time :
			if(sizeof($pkid)<1){
				return '0';
			} else if (sizeof($pkid) == 1){
				$query = "DELETE FROM m_job WHERE job_id = ".$pkid[0];
				$this->db->query($query);
			} else {
				$query = "DELETE FROM m_job WHERE ";
				for($i = 0; $i < sizeof($pkid); $i++){
					$query = $query . "job_id= ".$pkid[$i];
					if($i<sizeof($pkid)-1){
						$query = $query . " OR ";
					}     
				}
				$this->db->query($query);
			}
			if($this->db->affected_rows()>0)
				return '1';
			else
				return '0';
		}
		
		//function for advanced search record
		function m_job_search($job_id ,$job_name ,$job_active ,$job_created_date ,$job_created_by ,$job_updated_date ,$job_updated_by ,$job_updated_count ,$m_div_id ,$start,$end){
			//full query
			$query="select * from m_job";
			
			if($job_id!=''){
				$query .=preg_match("/\bWHERE\b/i",$query)? " AND ":" WHERE ";
				$query.= " job_id LIKE '%".$job_id."%'";
			};
			if($job_name!=''){
				$query .=preg_match("/\bWHERE\b/i",$query)? " AND ":" WHERE ";
				$query.= " job_name LIKE '%".$job_name."%'";
			};
			if($job_active!=''){
				$query .=preg_match("/\bWHERE\b/i",$query)? " AND ":" WHERE ";
				$query.= " job_active LIKE '%".$job_active."%'";
			};
			if($job_created_date!=''){
				$query .=preg_match("/\bWHERE\b/i",$query)? " AND ":" WHERE ";
				$query.= " job_created_date LIKE '%".$job_created_date."%'";
			};
			if($job_created_by!=''){
				$query .=preg_match("/\bWHERE\b/i",$query)? " AND ":" WHERE ";
				$query.= " job_created_by LIKE '%".$job_created_by."%'";
			};
			if($job_updated_date!=''){
				$query .=preg_match("/\bWHERE\b/i",$query)? " AND ":" WHERE ";
				$query.= " job_updated_date LIKE '%".$job_updated_date."%'";
			};
			if($job_updated_by!=''){
				$query .=preg_match("/\bWHERE\b/i",$query)? " AND ":" WHERE ";
				$query.= " job_updated_by LIKE '%".$job_updated_by."%'";
			};
			if($job_updated_count!=''){
				$query .=preg_match("/\bWHERE\b/i",$query)? " AND ":" WHERE ";
				$query.= " job_updated_count LIKE '%".$job_updated_count."%'";
			};
			if($m_div_id!=''){
				$query .=preg_match("/\bWHERE\b/i",$query)? " AND ":" WHERE ";
				$query.= " m_div_id LIKE '%".$m_div_id."%'";
			};
			
			$result = $this->db->query($query);
			$nbrows = $result->num_rows();
			$limit = $query." LIMIT ".$start.",".$end;		
			$result = $this->db->query($limit);   
			
			if($nbrows>0){
				foreach($result->result() as $row){
					$arr[] = $row;
				}
				$jsonresult = json_encode($arr);
				return '({"total":"'.$nbrows.'","results":'.$jsonresult.'})';
			} else {
				return '({"total":"0", "results":""})';
			}
		}
		
		//function for print record
		function m_job_print($job_id ,$job_name ,$job_active ,$job_created_date ,$job_created_by ,$job_updated_date ,$job_updated_by ,$job_updated_count ,$m_div_id ,$option,$filter){
			//full query
			$sql="SELECT job_id, job_kode,job_name, job_active, div_id, div_name, dvo_id, dvo_name
					FROM m_job LEFT JOIN m_div ON(m_div.div_id = m_job.m_div_id)
					LEFT JOIN m_dvo ON(m_dvo.dvo_id = m_job.m_dvo_id)";
			if($option=='LIST'){
				$sql .=preg_match("/\bWHERE\b/i",$sql)? " AND ":" WHERE ";
				$sql .= " (job_id LIKE '%".addslashes($filter)."%' OR job_name LIKE '%".addslashes($filter)."%' OR job_active LIKE '%".addslashes($filter)."%' OR job_created_date LIKE '%".addslashes($filter)."%' OR job_created_by LIKE '%".addslashes($filter)."%' OR job_updated_date LIKE '%".addslashes($filter)."%' OR job_updated_by LIKE '%".addslashes($filter)."%' OR job_updated_count LIKE '%".addslashes($filter)."%' OR m_div_id LIKE '%".addslashes($filter)."%' )";
				$query = $this->db->query($sql);
			} else if($option=='SEARCH'){
				if($job_id!=''){
					$sql .=preg_match("/\bWHERE\b/i",$sql)? " AND ":" WHERE ";
					$sql.= " job_id LIKE '%".$job_id."%'";
				};
				if($job_name!=''){
					$sql .=preg_match("/\bWHERE\b/i",$sql)? " AND ":" WHERE ";
					$sql.= " job_name LIKE '%".$job_name."%'";
				};
				if($job_active!=''){
					$sql .=preg_match("/\bWHERE\b/i",$sql)? " AND ":" WHERE ";
					$sql.= " job_active LIKE '%".$job_active."%'";
				};
				if($job_created_date!=''){
					$sql .=preg_match("/\bWHERE\b/i",$sql)? " AND ":" WHERE ";
					$sql.= " job_created_date LIKE '%".$job_created_date."%'";
				};
				if($job_created_by!=''){
					$sql .=preg_match("/\bWHERE\b/i",$sql)? " AND ":" WHERE ";
					$sql.= " job_created_by LIKE '%".$job_created_by."%'";
				};
				if($job_updated_date!=''){
					$sql .=preg_match("/\bWHERE\b/i",$sql)? " AND ":" WHERE ";
					$sql.= " job_updated_date LIKE '%".$job_updated_date."%'";
				};
				if($job_updated_by!=''){
					$sql .=preg_match("/\bWHERE\b/i",$sql)? " AND ":" WHERE ";
					$sql.= " job_updated_by LIKE '%".$job_updated_by."%'";
				};
				if($job_updated_count!=''){
					$sql .=preg_match("/\bWHERE\b/i",$sql)? " AND ":" WHERE ";
					$sql.= " job_updated_count LIKE '%".$job_updated_count."%'";
				};
				if($m_div_id!=''){
					$sql .=preg_match("/\bWHERE\b/i",$sql)? " AND ":" WHERE ";
					$sql.= " m_div_id LIKE '%".$m_div_id."%'";
				};
				$query = $this->db->query($sql);
			}
			return $query->result();
		}
		
		//function  for export to excel
		function m_job_export_excel($job_id ,$job_name ,$job_active ,$job_created_date ,$job_created_by ,$job_updated_date ,$job_updated_by ,$job_updated_count ,$m_div_id ,$option,$filter){
			//full query
			$sql="SELECT job_id, job_kode,job_name, job_active, div_id, div_name, dvo_id, dvo_name
					FROM m_job LEFT JOIN m_div ON(m_div.div_id = m_job.m_div_id)
					LEFT JOIN m_dvo ON(m_dvo.dvo_id = m_job.m_dvo_id)";
			if($option=='LIST'){
				$sql .=preg_match("/\bWHERE\b/i",$sql)? " AND ":" WHERE ";
				$sql .= " (job_id LIKE '%".addslashes($filter)."%' OR job_name LIKE '%".addslashes($filter)."%' OR job_active LIKE '%".addslashes($filter)."%' OR job_created_date LIKE '%".addslashes($filter)."%' OR job_created_by LIKE '%".addslashes($filter)."%' OR job_updated_date LIKE '%".addslashes($filter)."%' OR job_updated_by LIKE '%".addslashes($filter)."%' OR job_updated_count LIKE '%".addslashes($filter)."%' OR m_div_id LIKE '%".addslashes($filter)."%' )";
				$query = $this->db->query($sql);
			} else if($option=='SEARCH'){
				if($job_id!=''){
					$sql .=preg_match("/\bWHERE\b/i",$sql)? " AND ":" WHERE ";
					$sql.= " job_id LIKE '%".$job_id."%'";
				};
				if($job_name!=''){
					$sql .=preg_match("/\bWHERE\b/i",$sql)? " AND ":" WHERE ";
					$sql.= " job_name LIKE '%".$job_name."%'";
				};
				if($job_active!=''){
					$sql .=preg_match("/\bWHERE\b/i",$sql)? " AND ":" WHERE ";
					$sql.= " job_active LIKE '%".$job_active."%'";
				};
				if($job_created_date!=''){
					$sql .=preg_match("/\bWHERE\b/i",$sql)? " AND ":" WHERE ";
					$sql.= " job_created_date LIKE '%".$job_created_date."%'";
				};
				if($job_created_by!=''){
					$sql .=preg_match("/\bWHERE\b/i",$sql)? " AND ":" WHERE ";
					$sql.= " job_created_by LIKE '%".$job_created_by."%'";
				};
				if($job_updated_date!=''){
					$sql .=preg_match("/\bWHERE\b/i",$sql)? " AND ":" WHERE ";
					$sql.= " job_updated_date LIKE '%".$job_updated_date."%'";
				};
				if($job_updated_by!=''){
					$sql .=preg_match("/\bWHERE\b/i",$sql)? " AND ":" WHERE ";
					$sql.= " job_updated_by LIKE '%".$job_updated_by."%'";
				};
				if($job_updated_count!=''){
					$sql .=preg_match("/\bWHERE\b/i",$sql)? " AND ":" WHERE ";
					$sql.= " job_updated_count LIKE '%".$job_updated_count."%'";
				};
				if($m_div_id!=''){
					$sql .=preg_match("/\bWHERE\b/i",$sql)? " AND ":" WHERE ";
					$sql.= " m_div_id LIKE '%".$m_div_id."%'";
				};
				$query = $this->db->query($sql);
			}
			return $query->result();
		}
		
}
?>